package cn.zhaotianen.codegenerator.tools;

import cn.zhaotianen.codegenerator.domain.Column;
import cn.zhaotianen.codegenerator.domain.Table;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * <p/>
 * <pre>
 * ***************************************************************
 *  Copyright (c) 2014-2017 –苏州犀牛网络科技有限公司
 *  Package: cn.zhaotianen.codegenerator.tools
 *  Description:
 *  @since 1.0.0
 *  @author 赵天恩
 *  @date 2017/2/21
 *  @time 11:33
 * ***************************************************************
 * </pre>
 */
public class DBConnectionTool {

    public static final String CLASS_NAME="com.mysql.jdbc.Driver";
    public static final String URL="jdbc:mysql://127.0.0.1:3306/springbootdb";
    public static final String USER="root";
    public static final String PASSWORD="toor";

    /**
     * 连接数据库
     * @return
     */
    public static Connection getConnection() {
        Connection conn = null;
        try {
            Class.forName(CLASS_NAME);
            String url = URL;
            String user = USER;
            String pass = PASSWORD;
            conn = DriverManager.getConnection(url, user, pass);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    public static List<Table> getTables() {
        List<Table> tables = new ArrayList<Table>();
        try {
            // 获取表的信息
            DatabaseMetaData databaseMetaData = getConnection().getMetaData();

            // catalog: 要获得表所在的编目, 串""意味着没有任何编目, Null表示所有编目
            // schema: 要获得表所在的模式, 串""意味着没有任何模式, Null表示所有模式, 该参数可以包含单字符的通配符("_"), 也可以包含多字符的通配符("%")
            // tableName: 指出要返回表名与该参数匹配的那些表, 该参数可以包含单字符的通配符("_"), 也可以包含多字符的通配符("%")
            // types: 一个指出返回何种表的数组, 可能的数组项是: "TABLE", "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYSNONYM"
            ResultSet tableResult = databaseMetaData.getTables(null, null, null, new String[] {"TABLE"});

            // 遍历表的名字。
            while(tableResult.next()) {
                String tableName = tableResult.getString("TABLE_NAME");
                ResultSet columnResult = databaseMetaData.getColumns(null, null, tableName, null);

                List<Column> columns = new ArrayList<Column>();
                while(columnResult.next()) {
                    Column column = new Column();
                    column.setName(columnResult.getString("COLUMN_NAME"));
                    column.setTypeName(columnResult.getString("TYPE_NAME"));
                    column.setClassName(JDBCTypesUtils.jdbcTypeToJavaType(columnResult.getInt("DATA_TYPE")).getName());
                    column.setDescription(columnResult.getString("REMARKS"));
                    columns.add(column);
                };

                Table table = new Table();
                table.setName(tableName);
                table.setDescription(tableResult.getString("REMARKS"));
                table.setColumns(columns);
                tables.add(table);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return tables;
    }
}
